﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using ServerFramework.Tools;

namespace ServerFramework.Db
{
    /// <summary>
    /// 数据库提供者
    /// </summary>
    public abstract class DbProvider : IDisposable
    {
        protected DbConnection m_conn = null;
        private DbDataReader m_reader = null;
        public DbConnection Conn { get { return m_conn; } }
        private int m_col = 0;
        private String m_connectionString;

        public DbProvider(string connectionString)
        {
            m_connectionString = connectionString;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        public bool Open()
        {
            if (m_conn == null)
            {
                try
                {
                    if (string.IsNullOrEmpty(m_connectionString))
                    {
                        return false;
                    }
                    m_conn = CreateConnection(m_connectionString);
                    if (m_conn == null)
                    {
                        return false;
                    }
                    m_conn.Open();
                }
                catch (Exception e)
                {
                    Log.WriteLog(ELogLevel.LOG_ERROR, e);
                    m_conn.Close();
                    return false;
                }
            }
            return true;
        }


        /// <summary>
        /// 尝试打开数据库连接
        /// </summary>
        /// <returns></returns>
        public bool TryOpen()
        {
            if (m_conn == null)
            {
                try
                {
                    if (string.IsNullOrEmpty(m_connectionString))
                    {
                        return false;
                    }
                    m_conn = CreateConnection(m_connectionString);
                    if (m_conn == null)
                    {
                        return false;
                    }
                    m_conn.Open();
                }
                catch (Exception e)
                {
                    m_conn.Close();
                    return false;
                }
            }
            return true;
        }


        /// <summary>
        /// 创建连接
        /// </summary>
        /// <returns></returns>
        protected virtual DbConnection CreateConnection(string connString)
        {
            return null;
        }

        /// <summary>
        /// 创建命令
        /// </summary>
        /// <returns></returns>
        protected virtual DbCommand CreateCommand(string sql)
        {
            return null;
        }

        /// <summary>
        /// 创建命令
        /// </summary>
        /// <returns></returns>
        protected virtual DbCommand CreateCommand()
        {
            return null;
        }

        /// <summary>
        /// 创建一个参数
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="paramType"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public virtual DbParameter CreateParameter(string paramName,int paramType,object paramValue)
        {
            return null;
        }

        public void Close()
        {
            if (m_reader != null)
            {
                m_reader.Close();
                m_reader = null;
            }
            if (m_conn != null)
            {
                m_conn.Close();
                m_conn = null;
            }
        }

        /// <summary>
        /// 数据库表是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool IsTableExist(string tableName)
        {
            string sql = string.Format("select count(*) from {0}", tableName);
            DbCommand cm = CreateCommand(sql);
            bool isExist = false;
            try
            {
                cm.ExecuteNonQuery();
                isExist = true;
            }
            catch (Exception)
            {
                isExist = false;
            }
            cm.Dispose();
            return isExist;
        }

        /// <summary>
        /// 得到最大值
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public int GetMaxID(string TableName)
        {
            string strsql = "select count(*) from " + TableName;
            object obj = ExecuteScalar(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        /// <summary>
        /// 执行非查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            int ret = 0;
            using (DbCommand cm = CreateCommand(sql))
            {
                try
                {
                    ret = cm.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Log.WriteLog(ELogLevel.LOG_ERROR, e);
                }
            }
            return ret;
        }

        /// <summary>
        /// 执行非查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Params">参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params DbParameter[] Params)
        {
            int ret = 0;
            DbCommand cm = CreateCommand(sql);
            foreach (DbParameter par in Params)
            {
                cm.Parameters.Add(par);
            }
            try
            {
                ret = cm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            cm.Dispose();
            return ret;
        }

        public object ExecuteScalar(string sql)
        {
            object ret = null;
            using (DbCommand cm = CreateCommand(sql))
            {
                try
                {
                    ret = cm.ExecuteScalar();
                }
                catch (Exception e)
                {
                    Log.WriteLog(ELogLevel.LOG_ERROR, string.Format("{0};info[instance:{1}methed:{2}stack:{3}", e.Message, e.Source, e.TargetSite.Name, e.StackTrace));
                }
            }
            return ret;
        }

        public int ExecuteScalarToInt(string sql)
        {
            object ret = null;
            using (DbCommand cm = CreateCommand(sql))
            {
                try
                {
                    ret = cm.ExecuteScalar();
                }
                catch (Exception e)
                {
                    Log.WriteLog(ELogLevel.LOG_ERROR, string.Format("{0};info[instance:{1}methed:{2}stack:{3}", e.Message, e.Source, e.TargetSite.Name, e.StackTrace));
                }
            }
            if (ret == null)
            {
                return 0;
            }
            return int.Parse(ret.ToString()) ;
        }



        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public int ExecuteSqlTran(List<string> SQLStringList)
        {
            using (DbCommand cm = CreateCommand())
            {
                DbTransaction tx = m_conn.BeginTransaction();
                cm.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cm.CommandText = strsql;
                            count += cm.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch(Exception e)
                {
                    tx.Rollback();
                    Log.WriteLog(ELogLevel.LOG_ERROR, e);
                    return 0;
                }
            }
        }

        public bool ExecuteQuery(string sql)
        {
            try
            {
                DbCommand cm = CreateCommand(sql);
                m_reader = cm.ExecuteReader();
                cm.Dispose();
            }
            catch (Exception e)
            {
                Log.WriteLog(ELogLevel.LOG_ERROR,e);
                EndExecuteQuery();
                return false;
            }
            return true;
        }

        public void EndExecuteQuery()
        {
            if (m_reader != null)
            {
                m_col = 0;
                m_reader.Close();
                m_reader = null;
            }
        }
        public bool Read()
        {
            bool ret = false;
            try
            {

                if (m_reader != null)
                {
                    m_col = 0;
                    ret = m_reader.Read();
                }
            }
            catch (Exception e)
            {
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
                EndExecuteQuery();
                return ret;
            }
            return ret;
        }
        public object Read(int index)
        {
            object val = null;
            try
            {
               val = m_reader[index];
            }
            catch(Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR,e);
            }
            return val;
        }


        public object Read(string colName)
        {
            object val = null;
            try
            {
                val = m_reader[colName];
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return val;
        }

        public DbProvider Read(ref byte val)
        {
            try
            {
                val = (byte)m_reader[m_col];
                m_col++;
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return this;
        }

        public DbProvider Read(out byte[] val)
        {
            val = null;
            try
            {
                if (m_reader[m_col].Equals(DBNull.Value))
                {
                    val = new byte[10];
                }
                else
                {
                    val = (byte[])m_reader[m_col];
                }
                m_col++;
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return this;
        }

        public DbProvider Read(ref short val)
        {
            try
            {
                val = (short)m_reader[m_col];
                m_col++;
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return this;
        }

        public DbProvider Read(ref int val)
        {
            try
            {
                val = (int)m_reader[m_col];
                m_col++;
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }

            return this;
        }

        public DbProvider Read(ref uint val)
        {
            try
            {
                val = (uint)((int)m_reader[m_col]);

                m_col++;
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return this;
        }

        public DbProvider Read(ref float val)
        {
            try
            {
                val = (float)m_reader[m_col];
                m_col++;
            }

            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return this;
        }

        public DbProvider Read(ref bool val)
        {
            try
            {
                val = (bool)m_reader[m_col];
                m_col++;
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return this;
        }

        public DbProvider Read(ref long val)
        {
            try
            {
                val = (long)m_reader[m_col];
                m_col++;
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return this;
        }

        public DbProvider Read(out string val)
        {
            val = null;
            try
            {
                val = m_reader[m_col].ToString();
                m_col++;
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return this;
        }

        public DbProvider Read(out DateTime? val)
        {
            val = null;
            try
            {
                string tempStr = m_reader[m_col].ToString();
                val = DateTime.Parse(tempStr);
                m_col++;
            }
            catch (Exception e)
            {
                EndExecuteQuery();
                Log.WriteLog(ELogLevel.LOG_ERROR, e);
            }
            return this;
        }


        public void Dispose()
        {
            Close();
        }
    }
}
